package myta.bean;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.faces.component.html.HtmlInputText;
import javax.faces.component.html.HtmlSelectOneMenu;
import javax.faces.model.SelectItem;

import myta.base.FormDataBase;
import myta.model.DataRow;
import myta.model.DataTable;
import myta.service.Command;
import myta.service.SQLexec;
import myta.service.Utility;

public class NilaiData extends FormDataBase{
	private HtmlInputText txtid;
	private HtmlSelectOneMenu txtidjenisnilai;
	private HtmlSelectOneMenu txtidkelassiswa;
	private HtmlSelectOneMenu txtidmapel;
	private HtmlInputText txtnamaguru;
	
	private List<SelectItem> listIdJenisNilai;
	private List<SelectItem> listIdKelasSiswa;
	private List<SelectItem> listIdMapel;
	
	private DataDetail dd;
	
	@Override
	public void init() throws Exception {
		formName = "nilai";
		dd = (DataDetail)Utility.getBean("dataDetail");
		
		txtid = new HtmlInputText();
		txtidjenisnilai = new HtmlSelectOneMenu();
		txtidkelassiswa = new HtmlSelectOneMenu();
		txtidmapel = new HtmlSelectOneMenu();
		txtnamaguru = new HtmlInputText();
		
		loadList();
	}
	
	private void loadList() throws Exception{
		listIdJenisNilai = new ArrayList<SelectItem>();
		listIdKelasSiswa = new ArrayList<SelectItem>();
		listIdMapel = new ArrayList<SelectItem>();
		
		SelectItem s = new SelectItem();
		s.setValue("");
		s.setLabel("-");
		
		listIdJenisNilai.add(s);
		listIdKelasSiswa.add(s);
		listIdMapel.add(s);
		
		String sql = "select ks.idkelassiswa, concat(k.namakelas,' ~ ',s.thnajaran) as namakelas from kelassiswa ks inner join kelas k on k.idkelas = ks.idkelas inner join semester s on k.idsemester = s.idsemester";
		Command command = SQLexec.makeCommand(sql);
		DataTable dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			s = new SelectItem();
			s.setValue(dr.getString("idkelassiswa"));
			s.setLabel(dr.getString("namakelas"));
			
			listIdKelasSiswa.add(s);
		}
		
		sql = "select idjenisnilai, jenisnilai from jenisnilai";
		command = SQLexec.makeCommand(sql);
		dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			s = new SelectItem();
			s.setValue(dr.getString("idjenisnilai"));
			s.setLabel(dr.getString("jenisnilai"));
			
			listIdJenisNilai.add(s);
		}
		
		sql = "select idmapel,namamapel from mapel";
		command = SQLexec.makeCommand(sql);
		dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			s = new SelectItem();
			s.setValue(dr.getString("idmapel"));
			s.setLabel(dr.getString("namamapel"));
			
			listIdMapel.add(s);
		}
	}
	
	public void loadDataSiswa(){
		try {
			String idkelassiswa = txtidkelassiswa.getValue().toString();
			
			String sql = "select concat('') as idnilai, s.nis, s.nama, concat('0') as nilai from kelassiswa ks ";
			sql += "inner join kelassiswa_detail ksd on ks.idkelassiswa = ksd.idkelassiswa ";
			sql += "inner join siswa s on ksd.nis = s.nis ";
			sql += "where ks.idkelassiswa = ?";
			Command command =SQLexec.makeCommand(sql);
			command.setObject(1, idkelassiswa);
			DataTable dt = command.executeQuery();
			dd.getDataNilai().removeAll();
			
			for(int i=0;i<dt.getRowCount();i++){
				dd.getDataNilai().addRow(dt.getRow(i));
			}
			
			listIdMapel = new ArrayList<SelectItem>();
			sql = "select DISTINCT m.idmapel,m.namamapel from jadwal j inner join mapel m on j.idmapel = m.idmapel where j.idkelassiswa = ?";
			command = SQLexec.makeCommand(sql);
			command.setObject(1, idkelassiswa);
			dt = command.executeQuery();
			
			for(int i=0;i<dt.getRowCount();i++){
				DataRow dr = dt.getRow(i);
				
				SelectItem s = new SelectItem();
				s.setValue(dr.getString("idmapel"));
				s.setLabel(dr.getString("namamapel"));
				
				listIdMapel.add(s);
			}
		} catch (Exception e) {
			Utility.showMessage(e.getMessage());
		}
	}
	
	public void loadNamaGuru(){
		try {
			String idkelassiswa = txtidkelassiswa.getValue().toString();
			String idmapel = txtidmapel.getValue().toString();
			
			String sql = "select DISTINCT g.nama from jadwal j ";
			sql += "inner join mapel m on j.idmapel = m.idmapel ";
			sql += "inner join guru g on j.idguru = g.idguru ";
			sql += "where j.idkelassiswa = ? and m.idmapel = ?";
			Command command =SQLexec.makeCommand(sql);
			command.setObject(1, idkelassiswa);
			command.setObject(2, idmapel);
			DataTable dt = command.executeQuery();
			txtnamaguru.setValue(dt.getRow(0).getString("nama"));
		} catch (Exception e) {
			Utility.showMessage(e.getMessage());
		}
	}
	
	@Override
	public void getRequestData(Map<String, String> map) throws Exception {}

	@Override
	public boolean simpan() throws Exception {
		boolean boo = false;
		
		String sql = "INSERT INTO nilai(idnilai,idkelassiswa,idmapel,idjenisnilai) VALUES(?,?,?,?);";
		Command command = SQLexec.makeCommand(sql);
		command.setObject(1, txtid.getValue().toString());
		command.setObject(2, txtidkelassiswa.getValue().toString());
		command.setObject(3, txtidmapel.getValue().toString());
		command.setObject(4, txtidjenisnilai.getValue().toString());
		boo = command.executeUpdate();
		
		for(int i=0;i<dd.getDataNilai().getRowCount();i++){
			DataRow dr = dd.getDataNilai().getRow(i);
			String idnilai = txtid.getValue().toString();
			String nis = dr.getString("nis");
			Integer nilai = dr.getInteger("Nilai");
			
			sql = "insert into nilai_detail(idnilai,nis,nilai) values(?,?,?)";
			command = SQLexec.makeCommand(sql);
			command.setObject(1, idnilai);
			command.setObject(2, nis);
			command.setObject(3, nilai);
			boo = command.executeUpdate();
		}
		
		
		if(boo){
			return true;
		}else{
			return false;
		}		
	}

	@Override
	public boolean ubah() throws Exception {
		return true;
	}

	public HtmlSelectOneMenu getTxtidjenisnilai() {
		return txtidjenisnilai;
	}

	public void setTxtidjenisnilai(HtmlSelectOneMenu txtidjenisnilai) {
		this.txtidjenisnilai = txtidjenisnilai;
	}

	public HtmlSelectOneMenu getTxtidkelassiswa() {
		return txtidkelassiswa;
	}

	public void setTxtidkelassiswa(HtmlSelectOneMenu txtidkelassiswa) {
		this.txtidkelassiswa = txtidkelassiswa;
	}

	public List<SelectItem> getListIdJenisNilai() {
		return listIdJenisNilai;
	}

	public void setListIdJenisNilai(List<SelectItem> listIdJenisNilai) {
		this.listIdJenisNilai = listIdJenisNilai;
	}

	public List<SelectItem> getListIdKelasSiswa() {
		return listIdKelasSiswa;
	}

	public void setListIdKelasSiswa(List<SelectItem> listIdKelasSiswa) {
		this.listIdKelasSiswa = listIdKelasSiswa;
	}

	@Override
	public void generateID() throws Exception {
		String sql = "call idnilai()";
		Command command = SQLexec.makeCommand(sql);
		DataTable datatable = command.executeQuery();
		DataRow datarow = datatable.getRow(0);
		txtid.setValue(datarow.getString("id"));
	}

	public HtmlSelectOneMenu getTxtidmapel() {
		return txtidmapel;
	}

	public void setTxtidmapel(HtmlSelectOneMenu txtidmapel) {
		this.txtidmapel = txtidmapel;
	}

	public List<SelectItem> getListIdMapel() {
		return listIdMapel;
	}

	public void setListIdMapel(List<SelectItem> listIdMapel) {
		this.listIdMapel = listIdMapel;
	}

	public HtmlInputText getTxtid() {
		return txtid;
	}

	public void setTxtid(HtmlInputText txtid) {
		this.txtid = txtid;
	}

	public HtmlInputText getTxtnamaguru() {
		return txtnamaguru;
	}

	public void setTxtnamaguru(HtmlInputText txtnamaguru) {
		this.txtnamaguru = txtnamaguru;
	}
}
